﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using TanWeb.Models.Entities;
using TanWeb.Models.DataAccess.Infrastructure;
using TanWeb.Helpers;

namespace TanWeb.Models.DataAccess
{
    public class SubItemGroupDataAccess : BaseDataAccess
    {
        //===============================================================
        //  コンストラクタ等宣言
        //===============================================================
        #region /***    Constructor Destructor Declared    ***/

        /// <summary>
        /// コンストラクタ
        /// </summary>
        /// <param name="dbContext">データベースコンテキスト</param>
        public SubItemGroupDataAccess(DbContext dbContext) : base(dbContext) { }

        public List<SubItemGroup> GetSubItemsByGroupId(int groupId)
        {
            List<SubItemGroup> lstSubItemGroup = new List<SubItemGroup>();

            // ログを記述する
            logger.Info("\t\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action: GetAllItemGroup - GroupId = " + groupId.ToString());
            string sql = string.Empty;

            try
            {
                sql = string.Format(
                        @"SELECT *
                        FROM SubItemGroup 
                        WHERE GroupId = {0}
                        AND del_flg = 0
                        ORDER BY id DESC", groupId);
                lstSubItemGroup = this.DbContext.Database.SqlQuery<SubItemGroup>(sql).ToList();
            }
            catch (Exception ex)
            {
                logger.Error("\t\t\tError DataAccess: SubItemGroupDataAccess; Action: GetAllItemGroup", ex);
            }

            logger.Info("\t\t\tEnd DataAccess: SubItemGroupDataAccess; Action: GetAllItemGroup");

            return lstSubItemGroup;
        }

        public string RegisterSubGroup(string parentId, string groupName)
        {
            // ログを記述する
            logger.Info("\t\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");
            string sql = string.Empty;
            string date = DateTime.Now.ToString(Constants.FORMAT_FULL_DATE_TIME);
            int result = 0;

            try
            {
                sql = string.Format(
                    @"INSERT INTO SubItemGroup
                    VALUES ('{0}', N'{1}', 0, '{2}')"
                    , parentId, groupName, date);

                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup", ex);
            }

            logger.Info("\t\tEnd DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");

            return result.ToString();
        }

        public string UpdateSubGroup(string id, string groupName)
        {
            // ログを記述する
            logger.Info("\t\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");
            string sql = string.Empty;
            string date = DateTime.Now.ToString(Constants.FORMAT_FULL_DATE_TIME);
            int result = 0;

            try
            {
                sql = string.Format(
                    @"UPDATE SubItemGroup
                      SET SubGroupName = N'{0}',
                         UpdateDate = '{2}'
                      WHERE id = '{1}'
                    "
                    , groupName, id, date);

                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup", ex);
            }

            logger.Info("\t\tEnd DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");

            return result.ToString();
        }

        public List<SubItemGroup> GetSubGroup(string itemGroupId)
        {
            List<SubItemGroup> lstSubItemGroup = new List<SubItemGroup>();
            // ログを記述する
            logger.Info("\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action GetSubGroup");
            string sql = string.Empty;
            string condition = string.Empty;

            try
            {
                if (itemGroupId != null && itemGroupId != string.Empty && itemGroupId != "0")
                {
                    condition = string.Format("AND groupId = {0}", itemGroupId);
                }

                sql = string.Format(
                    @"SELECT * 
                      FROM SubItemGroup 
                      WHERE del_flg = 0
                        {0}
                      ORDER BY id DESC", condition);

                lstSubItemGroup = this.DbContext.Database.SqlQuery<SubItemGroup>(sql).ToList();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: SubItemGroupDataAccess; Action GetSubGroup", ex);
            }

            logger.Info("\t\tEnd DataAccess: SubItemGroupDataAccess; Action GetSubGroup");

            return lstSubItemGroup;
        }

        public SubItemGroup GetSubGroupById(string id)
        {
            SubItemGroup lstSubItemGroup = new SubItemGroup();
            // ログを記述する
            logger.Info("\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action GetSubGroupById");
            string sql = string.Empty;

            try
            {
                sql = string.Format(
                    @"SELECT * 
                      FROM SubItemGroup 
                      WHERE del_flg = 0
                        AND id = '{0}'", id);

                lstSubItemGroup = this.DbContext.Database.SqlQuery<SubItemGroup>(sql).FirstOrDefault();
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: SubItemGroupDataAccess; Action GetSubGroupById", ex);
            }

            logger.Info("\t\tEnd DataAccess: SubItemGroupDataAccess; Action GetSubGroupById");

            return lstSubItemGroup;
        }

        public string DeleteSubGroup(List<string> arrDel)
        {
            // ログを記述する
            logger.Info("\t\tBEGIN DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");
            string sql = string.Empty;
            string date = DateTime.Now.ToString(Constants.FORMAT_FULL_DATE_TIME);
            int result = 0;
            string condition = string.Empty;

            try
            {

                foreach (string delId in arrDel)
                {
                    condition += "'" + delId + "', ";
                }

                if (condition.Length > 1)
                {
                    condition = condition.Remove(condition.LastIndexOf(","));
                }

                sql = string.Format(
                    @"UPDATE SubItemGroup
                    SET del_flg = 1,
                        UpdateDate = '{0}'
                    WHERE id IN ({1})", date, condition);

                result = this.DbContext.Database.ExecuteSqlCommand(sql);
            }
            catch (Exception ex)
            {
                logger.Error("\t\tError DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup", ex);
            }

            logger.Info("\t\tEnd DataAccess: SubItemGroupDataAccess; Action RegisterSubGroup");

            return result.ToString();
        }

        #endregion

        
    }
}